/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */


/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package funciones;

/**
 *
 * @author jsunday
 */
import java.sql.*;
import java.util.StringTokenizer;
public class facturas2 {
     String res;
     String res2="";
     Statement st,st2,st3;
     ResultSet rs,rs2,rs3;
     Connection conn = null;
    public String factura(){

    try
    {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "BDR", "12345");
        st=conn.createStatement();
        String q="Select * from TBM_CLIENTES where CLI_ESTADO='1'";
        rs=st.executeQuery(q);
        while(rs.next()){
        //res=rs.getString("CLI_NOMBRE");
            res=res+"<option value="+rs.getString("CLI_ID")+">"+rs.getString("CLI_NOMBRE")+"</option>";

        }

//TBM_CLIENTES
    }

    catch(Exception e)
    {
        res="Exception : " + e.getMessage() + "";
    }
    return "<form ACTION=Fact-Generar.jsp METHOD=post><select NAME='articulo'>"+res+"</select><INPUT TYPE=submit VALUE='submit'></form>";

    }

    public String select(String x){
        try{
         conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "BDR", "12345");
        st=conn.createStatement();
        String q="select tbm_clientes.cli_nit,tbm_clientes.cli_nombre,tbt_trabajos_enc.doct_id,tbt_trabajos_enc.doct_nombre, tbt_trabajos_enc.doct_descripcion, tbt_trabajos_enc.doct_cantidad, tbt_trabajos_enc.doct_costo_total, tbt_trabajos_enc.doct_monto_total from tbm_clientes right join tbt_trabajos_enc on tbt_trabajos_enc.cli_id = tbm_clientes.cli_id where doct_estado_trabajo='1' and tbt_trabajos_enc.cli_id="+x;
        rs=st.executeQuery(q);
        while(rs.next()){
        //res=rs.getString("CLI_NOMBRE");
            res2=res2+"<tr><td><input type='checkbox' name='"+rs.getString("doct_id")+"'></td><td>"+rs.getString("CLI_NIT")+"</td><td>"+rs.getString("CLI_NOMBRE")+"</td><td>"+rs.getString("doct_nombre")+"</td><td>"+rs.getString("doct_descripcion")+"</td><td>"+rs.getString("doct_cantidad")+"</td><td>"+rs.getString("doct_costo_total")+"</td><td>"+rs.getString("doct_monto_total")+"</td></tr>";

        }
        }catch(Exception e){}

               return "<form ACTION=Fact-Generar.jsp METHOD=post><table><tr><th>Facturar</th><th>Nit</th><th>Cliente</th><th>Item</th><th>Descripcion</th><th>Cantidad</th><th>Costo uni.</th><th>Monto Total</th></tr>"+res2+"</table><span id='posicion'><INPUT TYPE=submit VALUE='Cancelar'><INPUT TYPE=submit VALUE='Facturar'></span></form>";

    }//fin funcion



    public int suma_total(String x){
        int suma=0;
        String tok="";
     try{
        StringTokenizer strtok2=new StringTokenizer(x,"% ");
        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "BDR", "12345");
        st=conn.createStatement();
        while(strtok2.hasMoreTokens()){
         tok=strtok2.nextToken();
         String sql="select doct_monto_total from tbt_trabajos_enc where doct_id="+tok;
         rs=st.executeQuery(sql);
             while(rs.next()){
                 suma=suma+rs.getInt("doct_monto_total");
        }
     }
     }catch(Exception e){}
        return suma;
    }

    public String select2(String x){
        String columna=x;
        String respuesta="";
        String ans="";
        String calculo_monto=x;
        String cliente="";
        String monto="";
        String detalles="";
        String fact="";
         int numfact=(int)(Math.random()*1000);
         fact=Integer.toString(numfact);
        int suma=suma_total(calculo_monto);
        try{
            monto=Integer.toString(suma);
        if (x.equalsIgnoreCase("%articulo")){
            return "";
        }


        StringTokenizer strtok =new StringTokenizer(x,"% ");
        while(strtok.hasMoreTokens()){
            respuesta=strtok.nextToken();
              cliente=obtener_cli(respuesta);
              factura_enc(numfact,cliente,monto);
              factura_det2(fact,respuesta);
              update(respuesta.toString(),cliente);

             }
            //actualizar=factura_det(detalles);


        ans=factura_col(columna);
    }catch(Exception e){}

       
        return "<table id='items'> <tr>\n" +
"		      <th>Item</th>\n" +
"		      <th>Descripcion</th>\n" +
"		      <th>Valor unitario</th>\n" +
"		      <th>Cantidad</th>\n" +
"		      <th>Monto total</th>\n" +
"		  </tr>"+ans+"<tr>\n" +
"\n" +
"		      <td colspan=\"2\" class=\"blank\"> </td>\n" +
"		      <td colspan=\"2\" class=\"total-line\">Total</td>\n" +
"		      <td class=\"total-value\"><div id=\"total\">"+suma+"</div></td>\n" +
"		  </tr></table>";

    }

    public String update(String x, String cliente){
        int contador=0;
        String ans="";
         try{
      conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "BDR", "12345");
        st=conn.createStatement();
        st2=conn.createStatement();
        st3=conn.createStatement();
         String sql="update tbt_trabajos_enc set doct_estado_trabajo='2' where doct_id="+x;
         rs=st.executeQuery(sql);

        //}
     }catch(Exception e){}
        return ans;
    }
    public String factura_enc(int numfact,String cli_id, String monto){

        String sql="insert into tbt_facturacion_enc (docf_id,cli_id,docf_fecha,docf_estado_factura,docf_porcentaje_pago, docf_monto_total ) values ("+numfact+","+cli_id+",'',"+0+","+0+","+monto+")";
          try{
        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "BDR", "12345");
        st=conn.createStatement();
        rs=st.executeQuery(sql);


    }catch(Exception e){}
          return "";
    }

    public String obtener_cli(String x){
        String sql="select cli_id from tbt_trabajos_enc where doct_id="+x;
        String cliente="";
        try{
        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "BDR", "12345");
        st2=conn.createStatement();
        rs2=st2.executeQuery(sql);
        while(rs2.next()){
            cliente=rs2.getString("CLI_ID");
        }

        }catch(Exception e){}
        return cliente;
    }


     public String factura_det2(String fact, String iddoct){
        String datos=obtener_datos(iddoct);
        String sql="insert into Tbt_facturacion_det (doct_id,   docf_id,  tdoc_id,  docfd_cantidad,   docfd_descripcion,   docfd_monto_Trabajo) values ("+iddoct+","+fact+","+datos+")";
        try{
        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "BDR", "12345");
        st2=conn.createStatement();
        rs2=st2.executeQuery(sql);

        }catch(Exception e){}
        return datos;
    }

     public String obtener_datos(String x){
         String sql="select cli_id,doct_id, tdoc_id, doct_fecha, doct_nombre, doct_descripcion, doct_costo_total, doct_cantidad, doct_monto_total from tbt_trabajos_enc where doct_id="+x;
         String datos="";
          try{
        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "BDR", "12345");
        st3=conn.createStatement();
        rs3=st3.executeQuery(sql);
        while(rs3.next()){
            datos=rs3.getString("tdoc_id")+","+rs3.getString("doct_cantidad")+",'"+rs3.getString("doct_descripcion")+"',"+rs3.getString("doct_monto_total");
        }
        }catch(Exception e){}
     return datos;
     }
    public String factura_col(String x){
        String respuesta="";
        String col="";
            try{
          conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "BDR", "12345");
          st3=conn.createStatement();


        StringTokenizer strtok =new StringTokenizer(x,"% ");
        while(strtok.hasMoreTokens()){
            respuesta=strtok.nextToken();
             String q="select cli_id,doct_id, tdoc_id, doct_fecha, doct_nombre, doct_descripcion, doct_costo_total, doct_cantidad, doct_monto_total from tbt_trabajos_enc where doct_id="+respuesta;
             rs3=st3.executeQuery(q);
            while(rs3.next()){
                col=col+"<tr id='item-row'><td class='item-name'>"+rs3.getString("doct_nombre")+"</td><td>"+rs3.getString("doct_descripcion")+"</td><td>"+rs3.getString("doct_costo_total")+"</td><td>"+rs3.getString("doct_cantidad")+"</td><td>"+rs3.getString("doct_monto_total")+"</td></tr>";


             }


        }
        }catch(Exception e){}
        return col;
    }
}//fin clase
